﻿Imports System.Data.Common

Public Class SelectData
    Inherits System.Web.UI.Page

    Dim DefaultQCodeFilter As String = ""
    Shared PrevPage As String = ""

#Region "Form Methods"

    Protected Overrides Sub InitializeCulture()
        With ProjectSettings
            System.Threading.Thread.CurrentThread.CurrentUICulture = .Language
            System.Threading.Thread.CurrentThread.CurrentCulture = .Language
        End With

        MyBase.InitializeCulture()
    End Sub

    ''' <summary>
    ''' Reset some html text using selected language (not automatically done like for ASP controls)
    ''' </summary>
    Private Sub Page_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        txtFilterString.Attributes("title") = GetLocalResourceObject("txtFilterResource.Title")
        cboSavedQueries.Focus() 'do this so after popping up a dialog that refreshes page, will focus here so Enter key does selection
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        hdnMustSelect.Value = "false"

        'Ivan wants the default QCode filter to normally exclude all but QA verified data; these will be indicated as QA in the description
        '6/11/13 Ivan want to remove because it is different than way that WRDB works and is causing confusion

        'For Each dr As DataRow In ProjectDataset.Tables("QCodes").Rows
        '    If dr("Description").ToString.StartsWith("QA") Then
        '        DefaultQCodeFilter &= IIf(DefaultQCodeFilter = "", "IN ('", ", '") & dr("QCode") & "'"
        '    End If
        'Next
        'If DefaultQCodeFilter <> "" Then DefaultQCodeFilter &= ")"

        If Not IsPostBack Then

            If Request.UrlReferrer IsNot Nothing Then PrevPage = Request.UrlReferrer.ToString

            With ProjectSettings
                If Not .Load Then Response.Redirect("Configure.aspx") : Exit Sub
                btnSelect.Attributes("onclick") = "javascript: document.body.style.cursor = 'wait';" 'when button clicked show wait cursor; when page reloads will be reset to default

                lblInfo.Text = String.Format(lblInfo.Text, NumTableRecords)
                FillQueryList()
                UpdateFilters("[LastQuery]")
                If ShowMustSelectError Then 'force display of warning dialog
                    ShowDialog(UpdatePanel1, pnlDialogMustSelect, GetGlobalResourceObject("WRDB", "MustSelect"), 400)
                    ShowMustSelectError = False
                End If
            End With

            'these put the "All" string as a background image that disappears when you start editing

            txtStationID.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtDateTime.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtPCode.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtCCode.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtRCode.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtQCode.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtSCode.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtLCode.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtResult.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtDepth.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            txtFilterString.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "All"))
            cboSavedQueries.Attributes.Add("placeholder", GetGlobalResourceObject("WRDB", "SelectList"))

        Else

            'must rebuild table used in assist dialog upon load so can retrieve values using viewstate on next postback
            Dim TableName As String = HttpContext.Current.Session("TableName")
            If TableName IsNot Nothing Then
                CreateSupportTable(TableName)
            End If

            'restore visibility of more fields
            If hdnMore.Value = "True" Then
                divMore.Style.Add("Display", "normal")
                btnMore.Style.Add("display", "none")
                btnLess.Style.Add("display", "normal")
            Else
                divMore.Style.Add("Display", "none")
                btnMore.Style.Add("display", "normal")
                btnLess.Style.Add("display", "none")
            End If

        End If
        lblSelect.Text = String.Format(GetLocalResourceObject("lblSelectResource1.Text"), NumSelectedRecords)

    End Sub
#End Region

#Region "Select Saved Query"

    ''' <summary>
    ''' fill dropdown list of local and global queries
    ''' </summary>
    ''' <remarks>Note: this must be consistent with similar method in JavaScript and other places where (Shared) is interpreted</remarks>
    Private Sub FillQueryList()

        Dim lst As New List(Of String)
        For Each key As String In ProjectSettings.LocalQueries.Keys
            If key.StartsWith(Project.Name) Then lst.Add(key.Replace(Project.Name, ""))
        Next
        For Each key As String In ProjectSettings.GlobalQueries.Keys
            If key.StartsWith(Project.Name) Then lst.Add(key.Replace(Project.Name, "") & " " & GetGlobalResourceObject("WRDB", "SharedText"))
        Next
        lst.Sort()
        'clear then add an empty one at the top
        cboSavedQueries.Items.Clear()
        cboSavedQueries.Items.Add(New ListItem(GetGlobalResourceObject("WRDB", "SelectList"), ""))
        'cboSavedQueries.Items.Add("")
        For Each key As String In lst
            cboSavedQueries.Items.Add(key)
        Next
        SelectAll()
    End Sub

    Private Sub cboSavedQueries_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSavedQueries.SelectedIndexChanged
        UpdateFilters(Project.Name & cboSavedQueries.Text)
    End Sub

    ''' <summary>
    ''' Given name of saved query, retrieve criteria and place in textboxes
    ''' </summary>
    ''' <param name="queryName">Name of saved query (if '( Shared)' appended, knows is global query; if is [LastQuery] then restore prior query filters)</param>
    Private Sub UpdateFilters(queryName As String)
        If queryName = GetGlobalResourceObject("WRDB", "SelectList") Then
            SelectAll()
        Else
            Dim filters As Dictionary(Of String, String) = Nothing
            If queryName = "[LastQuery]" Then
                filters = ProjectSettings.LastQuery
                If filters Is Nothing OrElse filters.Count = 0 Then
                    If DefaultQCodeFilter <> "" Then
                        filters.Add("QCode", DefaultQCodeFilter)
                    End If
                End If
            Else
                'select which dictionary is being used
                Dim dict As Dictionary(Of String, Dictionary(Of String, String)) = Nothing
                Dim SharedText As String = " " & GetGlobalResourceObject("WRDB", "SharedText")
                If queryName.EndsWith(SharedText) Then dict = ProjectSettings.GlobalQueries Else dict = ProjectSettings.LocalQueries
                queryName = queryName.Replace(SharedText, "")
                'possible that saved query was deleted by another user
                If dict.ContainsKey(queryName) Then filters = dict(queryName)
            End If
            If filters IsNot Nothing AndAlso filters.Count > 0 Then
                SelectAll() 'clear all fields
                For Each kv In filters
                    Dim txt As TextBox = FindControlRecursive(Me, "txt" & kv.Key) 'indirectly find textbox
                    If txt IsNot Nothing Then txt.Text = kv.Value 'set filter
                    'if one of the fields is in More table, show it
                    Select Case kv.Key
                        Case "Depth", "RCode", "LCode", "SCode"
                            If txt.Text <> "" Then divMore.Style("Display") = "normal"
                        Case "QCode"
                            If txt.Text <> DefaultQCodeFilter And txt.Text <> "" Then
                                divMore.Style("Display") = "normal"
                            End If
                    End Select
                Next
            End If
            End If
    End Sub

    Private Function FindControlRecursive(rootControl As Control, controlID As String) As Control
        If rootControl.ID = controlID Then Return rootControl
        For Each controlToSearch As Control In rootControl.Controls
            Dim controlToReturn As Control = FindControlRecursive(controlToSearch, controlID)
            If controlToReturn IsNot Nothing Then Return controlToReturn
        Next
        Return Nothing
    End Function

#End Region

#Region "Save/Delete Query"

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If txtStationID.Text & txtDateTime.Text & txtPCode.Text & txtCCode.Text & txtResult.Text & txtDepth.Text & txtRCode.Text & txtLCode.Text & txtSCode.Text & txtQCode.Text = "" Then
            'give error if forgot to fill in filters (reuse Delete dialog)
            lblDelete.Text = GetGlobalResourceObject("WRDB", "CannotSave")
            btnCancelDelete.Visible = False
            ShowDialog(UpdatePanel1, pnlDialogDelete, GetLocalResourceObject("lblSaveResource1.Title"), 400)
        Else
            'Overview:
            '1. set up dialog in hidden div for entering query name
            '2. use JQuery to display popup dialog

            'clear prior entries (note: asp control prepend to ID, so must use this jQuery form; want to use asp controls so will automatically convert languages)
            txtQueryName.Text = ""
            chkQueryName.Checked = False

            '2. use JQuery to display popup dialog
            ShowDialog(UpdatePanel1, pnlDialogSave, GetLocalResourceObject("lblSaveResource1.Title"), 700)
        End If
    End Sub

    ''' <summary>
    ''' Called when save query dialog box OK button is clicked
    ''' </summary>
    Private Sub btnOKSave_Click(sender As Object, e As EventArgs) Handles btnOKSave.Click

        Dim QueryName As String = txtQueryName.Text

        'if user entered query in all lower or upper case, convert to proper case
        If QueryName = QueryName.ToLower Or QueryName = QueryName.ToUpper Then
            QueryName = Strings.StrConv(QueryName, VbStrConv.ProperCase)
        End If

        Dim dict As Dictionary(Of String, Dictionary(Of String, String)) = IIf(chkQueryName.Checked, ProjectSettings.GlobalQueries, ProjectSettings.LocalQueries)
        With dict
            Dim filters As Dictionary(Of String, String) = GetFilters()
            If filters.Count > 0 Then
                If Not .ContainsKey(Project.Name & QueryName) Then .Add(Project.Name & QueryName, New Dictionary(Of String, String))
                .Item(Project.Name & QueryName) = filters
                ProjectSettings.Save()
                FillQueryList()
                Try
                    cboSavedQueries.Text = QueryName
                Catch ex As Exception
                    cboSavedQueries.Text = QueryName & " " & GetGlobalResourceObject("WRDB", "SharedText")
                End Try
                UpdateFilters(Project.Name & cboSavedQueries.Text)
            End If
        End With
    End Sub

    ''' <summary>
    ''' Delete Saved Query button
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim savedQuery As String = cboSavedQueries.Text

        If cboSavedQueries.SelectedIndex = 0 Then
            'give error if forgot to fill in filters
            lblDelete.Text = GetGlobalResourceObject("WRDB", "CannotDelete")
            btnCancelDelete.Visible = False
            ShowDialog(UpdatePanel1, pnlDialogDelete, GetLocalResourceObject("lblDeleteResource1.Title"), 400)
        Else
            'ask for confirmation
            lblDelete.Text = String.Format(GetLocalResourceObject("lblDeleteResource1.Text"), cboSavedQueries.Text)
            btnCancelDelete.Visible = True
            ShowDialog(UpdatePanel1, pnlDialogDelete, GetLocalResourceObject("lblDeleteResource1.Title"), 400)
        End If
    End Sub

    ''' <summary>
    ''' Called when OK button on delete dialog is clicked; removes saved query in list
    ''' </summary>
    Private Sub btnOKDelete_Click(sender As Object, e As EventArgs) Handles btnOKDelete.Click
        Dim QueryName As String = cboSavedQueries.Text()

        Dim dict As Dictionary(Of String, Dictionary(Of String, String)) = IIf(QueryName.Contains(" " & GetGlobalResourceObject("WRDB", "SharedText")), ProjectSettings.GlobalQueries, ProjectSettings.LocalQueries)
        QueryName = QueryName.Replace(" " & GetGlobalResourceObject("WRDB", "SharedText"), "")
        With dict
            If .ContainsKey(Project.Name & QueryName) Then .Remove(Project.Name & QueryName)
        End With
        ProjectSettings.Save()
        FillQueryList()
    End Sub

    'Private Sub btnCancelDelete_Click(sender As Object, e As EventArgs) Handles btnCancelDelete.Click
    '    'CloseDialog(UpdatePanel1, pnlDialogDelete)
    'End Sub

#End Region

#Region "Execute Query"

    ''' <summary>
    ''' Retrieve the entries in the filter textboxes and store in dictionary of fieldname/filter pairs
    ''' </summary>
    Private Function GetFilters() As Dictionary(Of String, String)
        Dim dict As New Dictionary(Of String, String)
        For i As Integer = 1 To 10
            Dim field As String = Choose(i, "StationID", "DateTime", "PCode", "CCode", "Result", "Depth", "RCode", "LCode", "SCode", "QCode")
            Dim filter As String = Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtResult, txtDepth, txtRCode, txtLCode, txtSCode, txtQCode).Text
            dict.Add(field, filter) 'always add empty strings so can tell if user cleared out QCode default
            'If Not String.IsNullOrEmpty(filter.Trim) Then
            '    dict.Add(field, filter)
            'End If
        Next
        Return dict
    End Function

    ''' <summary>
    ''' Retrieve settings from form and execute query
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
        ClearError()
        Dim dt As New DataTable
        If Not ProjectSettings.Load Then Response.Redirect("Configure.aspx") : Exit Sub
        Dim sql As String = String.Format("SELECT {2} Dta.* FROM {0} Dta {1} ORDER BY Station_ID, PCode, Date_Time",
                                          ProjectSettings.TableName,
                                          BuildSQLWhere,
                                          IIf(Project.DB.DatabaseType = WRDB.DBProviders.clsDB.enumDBType.SQL_Server, "TOP " & ProjectSettings.MaxRows, ""))
        LastSQL = sql
        Try
            Project.DB.AddTable(dt, sql, ProjectSettings.MaxRows)
            If dt.Rows.Count >= ProjectSettings.MaxRows Then 'assume row count was exceeded
                ShowError(String.Format(GetGlobalResourceObject("WRDB", "MaxRowsExceeded"), ProjectSettings.MaxRows))
                Exit Sub
            End If
        Catch ex As Exception
            If Project.DB.LastException IsNot Nothing Then
                ShowError(Project.DB.LastException.Message & "  The SQL statement was: " & sql)
            Else
                ShowError(ex.Message)
            End If
            Exit Sub
        End Try
        SelectedDataTable = dt
        ProjectSettings.LastQuery = GetFilters()
        lblSelect.Text = String.Format(GetLocalResourceObject("lblSelectResource1.Text"), dt.Rows.Count)

        If dt.Rows.Count > 0 Then
            If PrevPage.Contains("Report.aspx") Or
               PrevPage.Contains("Graph.aspx") Or
               PrevPage.Contains("Map.aspx") Or
               PrevPage.Contains("Export.aspx") Then
                Response.Redirect(PrevPage)
            Else
                Response.Redirect("ViewData.aspx")
            End If
        End If
    End Sub

    ''' <summary>
    ''' Display error text on form
    ''' </summary>
    ''' <param name="ErrorText">Text to display</param>
    Private Sub ShowError(ErrorText As String)
        lblError.Text = ErrorText.Replace("\n", "<BR />")
        lblError.Visible = True
    End Sub

    ''' <summary>
    ''' Hide error text
    ''' </summary>
    Private Sub ClearError()
        lblError.Visible = False
    End Sub

    ''' <summary>
    ''' Build filter list to perform query based on current entries on form
    ''' </summary>
    ''' <returns>Properly formatted SQL WHERE clause</returns>
    Friend Function BuildSQLWhere() As String

        Dim WhereClause As String = ""

        For i As Integer = 1 To 10
            With CType(Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtResult, txtDepth, txtRCode, txtLCode, txtSCode, txtQCode), TextBox)
                Dim FieldName As String = "Dta." & Choose(i, "Station_ID", "Date_Time", "PCode", "CCode", "Result", "Depth_M", "RCode", "LCode", "SCode", "QCode")
                Dim Value As String = .Text.Trim
                If i = 2 Then 'this field may have some fancy date extraction text like "EXTRACT(YEAR from Date_time)=2009"
                    If .Text.ToUpper.Contains("DATE_TIME") Then
                        FieldName = ""
                        Value = Value.Replace("Date_Time", "Dta.Date_Time")
                    End If
                End If
                If Value = "ALL" Or Value = "" Then
                    'leave clause empty for this field
                ElseIf Value.StartsWith("IN", StringComparison.OrdinalIgnoreCase) And Value.Contains("%") Then
                    'replace IN clause having wildcards with multiple LIKEs
                    Dim ar() As String = Value.Replace("IN (", "").Replace(")", "").Split(",")
                    Dim LikeClause As String = ""
                    For Each s As String In ar
                        LikeClause &= IIf(LikeClause = "", "", " OR ") & FieldName & " LIKE " & s
                    Next
                    WhereClause &= IIf(WhereClause = "", "(", " AND (") & LikeClause & ")"
                Else
                    'prepend field name only and assume rest of field is formatted correctly
                    WhereClause &= IIf(WhereClause = "", "", " AND ") & FieldName & " " & Value
                End If
            End With
        Next

        If WhereClause <> "" Then WhereClause = " WHERE " & WhereClause

        Return WhereClause

    End Function

    ''' <summary>
    ''' Replace wildcard characters that may have been entered by user with those recognized by SQL
    ''' </summary>
    ''' <param name="s">SQL string</param>
    Private Shared Function fixLikeWildcards(ByVal s As String) As String
        Return s.Replace("..", "%").Replace("*", "%").Replace("@", "_").Replace("?", "_")
    End Function

    Protected Sub btnView_Click(sender As Object, e As EventArgs) Handles btnView.Click
        Response.Redirect("ViewData.aspx")
    End Sub

    Private Sub txt_TextChanged(sender As Object, e As EventArgs) Handles txtStationID.TextChanged, txtPCode.TextChanged, txtCCode.TextChanged, txtLCode.TextChanged, txtSCode.TextChanged, txtQCode.TextChanged, txtRCode.TextChanged
        ParseString(CType(sender, TextBox).Text)
    End Sub

    ''' <summary>
    ''' Take list that user typed (or was inserted by assistant) and create proper SQL where clause
    ''' </summary>
    Private Sub ParseString(ByRef txt As String)

        'if assistant used tick marks, assume string is already properly formatted

        txt = fixLikeWildcards(txt)

        If txt.Trim = "" Or txt.ToUpper.Trim = "ALL" Then
            txt = ""
        Else
            If Not (txt.Contains("'") Or txt.ToUpper.Contains("NULL")) Then 'no quotes, so try to fix it up
                Dim ar() As String = txt.Split(",")
                If ar.Length = 1 Then 'is single item
                    If txt.Contains("%") Or txt.Contains("_") Then
                        txt = "LIKE '" & ar(0).ToUpper & "'"
                    Else
                        txt = "= '" & ar(0).ToUpper & "'"
                    End If
                Else 'is multiple items, make "In" list
                    txt = "IN ("
                    For i As Int16 = 0 To ar.Length - 1
                        txt &= IIf(i = 0, "'", ", '") & ar(i).Trim.ToUpper & "'"
                    Next
                    txt &= ")"
                End If
            End If
        End If
    End Sub

    Private Sub txtResult_TextChanged(sender As Object, e As EventArgs) Handles txtResult.TextChanged, txtDepth.TextChanged
        Dim txt As TextBox = sender
        If IsNumeric(txt.Text) Then 'single number entered; assume want =
            txt.Text = "= " & txt.Text
        ElseIf txt.Text.Contains(",") Then 'assume is list
            Dim ar() As String = txt.Text.Split(",")
            txt.Text = ""
            For i As Integer = 0 To ar.Length - 1
                If IsNumeric(ar(i)) Then txt.Text &= IIf(txt.Text = "", "IN (", ", ") & ar(i)
            Next
            txt.Text &= ")"
        End If
    End Sub

    Private Sub txtDateTime_TextChanged(sender As Object, e As EventArgs) Handles txtDateTime.TextChanged
        Dim txt As TextBox = sender
        If IsDate(txt.Text) Then 'single date entered; assume want all values on that date
            txt.Text = String.Format("BETWEEN {0} AND {1}", Project.DB.DateTime(CDate(txt.Text)), Project.DB.DateTime(CDate(txt.Text).AddDays(1).AddMinutes(-1)))
        End If
    End Sub

#End Region

#Region "Clear/Select All buttons"
    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        SelectAll()
        'clear the saved query
        cboSavedQueries.SelectedIndex = 0
    End Sub

    Private Sub SelectAll()
        ClearError()
        For i As Integer = 1 To 10
            With CType(Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtResult, txtDepth, txtRCode, txtLCode, txtSCode, txtQCode), TextBox)
                .Text = IIf(i = 10, DefaultQCodeFilter, "")
            End With
        Next
    End Sub
#End Region

#Region "Assist buttons (Support tables)"

    Private Sub btnSta_Click(sender As Object, e As EventArgs) Handles btnSta.Click
        ShowDialogSupport("Stations")
    End Sub

    Private Sub btnPCode_Click(sender As Object, e As EventArgs) Handles btnPCode.Click
        ShowDialogSupport("PCodes")
    End Sub

    Private Sub btnCCode_Click(sender As Object, e As EventArgs) Handles btnCCode.Click
        ShowDialogSupport("CCodes")
    End Sub

    Private Sub btnRCode_Click(sender As Object, e As EventArgs) Handles btnRCode.Click
        ShowDialogSupport("RCodes")
    End Sub

    Private Sub btnQCode_Click(sender As Object, e As EventArgs) Handles btnQCode.Click
        ShowDialogSupport("QCodes")
    End Sub

    Private Sub btnLCode_Click(sender As Object, e As EventArgs) Handles btnLCode.Click
        ShowDialogSupport("LCodes")
    End Sub

    Private Sub btnSCode_Click(sender As Object, e As EventArgs) Handles btnSCode.Click
        ShowDialogSupport("SCodes")
    End Sub

    Private Sub rblDisplay_SelectedIndexChanged(sender As Object, e As EventArgs) Handles rblDisplay.SelectedIndexChanged
        ShowDialogSupport()
    End Sub

    Private Sub btnFilterString_Click(sender As Object, e As EventArgs) Handles btnFilterString.Click
        ShowDialogSupport()
    End Sub

    Private Sub ShowDialogSupport(Optional TableName As String = "")

        If TableName = "" Then 'is already displayed; use last one
            TableName = HttpContext.Current.Session("TableName")
        Else
            HttpContext.Current.Session("TableName") = TableName
        End If

        'if Stations or PCode table, display option to select items or groups
        Dim showGroups As Boolean = (TableName = "Stations" Or TableName = "PCodes")

        lblDisplaySupport.Visible = showGroups
        rblDisplay.Visible = showGroups

        'don't allow filtering if showing groups
        If showGroups And rblDisplay.SelectedIndex = 1 Then
            txtFilterString.Text = ""
            pnlFilterString.Visible = False
        Else
            pnlFilterString.Visible = True
        End If

        'Overview:
        '1. query data from database and return from PageMethod as JSON object
        '2. use data to build table in hidden div
        '3. use JQuery to display popup dialog
        '4. when popup is closed, build string and place in textbox 

        'Let's get started:
        '1. query data from database and build table on page

        CreateSupportTable(TableName)

        '2. display table in dialog

        ShowDialog(UpdatePanel1, pnlDialogSupport, GetGlobalResourceObject("WRDB", "QueryAssist"), 900)

    End Sub

    ''' <summary>
    ''' PageMethod to retrieve records to fill Assist dialog box
    ''' </summary>
    ''' <param name="TableName">Name of Support Table</param>
    ''' <remarks></remarks>
    Private Sub CreateSupportTable(TableName As String)

        If Not ProjectSettings.Load Then Exit Sub

        Dim FieldList As String = "", FilterFields As String = ""

        Select Case TableName
            Case "Stations"
                FieldList = "Station_ID,Station_Name,Station_Type,Branch_ID,WaterBodyType,Basin_Name"
                FilterFields = "Station_ID,Station_Name,Station_Type,Branch_ID,WaterBodyType,Basin_Name"
            Case "PCodes"
                FieldList = "PCode,Anal_Name,Units,Test_Method,Det_Limit,Min_Limit,Max_Limit,Min_Criteria,Max_Criteria"
                FilterFields = "PCode,Anal_Name"
            Case "CCodes"
                FieldList = "CCode,CCode_Name,Description"
                FilterFields = "CCode,CCode_Name,Description"
            Case "RCodes"
                FieldList = "RCode,Description,Calc_Stats,NonDetect_PCode,NonDetect_Result"
                FilterFields = "RCode,Description"
            Case "QCodes"
                FieldList = "QCode,QCode_Name,Description"
                FilterFields = "QCode,QCode_Name,Description"
            Case "SCodes"
                FieldList = "SCode"
                FilterFields = ""
            Case "LCodes"
                FieldList = "LCode"
                FilterFields = ""
        End Select

        Dim dt As New DataTable

        Debug.Assert(Project IsNot Nothing, "Project is not set!")

        'if querying stations or pcodes, add additional records from groups table; will add one row for each group, and assemble Stations or PCodes into list
        If rblDisplay.SelectedIndex = 1 AndAlso (TableName.ToUpper = "STATIONS" Or TableName.ToUpper = "PCODES") Then

            dt.Columns.Add("Group Name", GetType(String))
            dt.Columns.Add("Items in Group", GetType(String))
            Dim dtGrp As New DataTable
            Project.DB.AddTable(dtGrp, String.Format("SELECT * FROM {0} GRP ORDER BY {1}", IIf(TableName.ToUpper = "STATIONS", "STAGRP", "PCODEGRP"), IIf(TableName.ToUpper = "STATIONS", "STATIONGROUP", "PCODEGROUP")))
            Dim LastGrp As String = ""
            Dim drAdded As DataRow = Nothing
            For Each dr As DataRow In dtGrp.Rows
                If Not IsDBNull(dr(0)) AndAlso dr(0) <> LastGrp Then
                    drAdded = dt.NewRow
                    drAdded(0) = dr(0)
                    drAdded(1) = dr(1)
                    dt.Rows.Add(drAdded)
                    LastGrp = dr(0)
                Else
                    drAdded(1) &= "," & dr(1)
                End If
            Next

        Else

            If TableName = "SCodes" Or TableName = "LCodes" Then
                Project.DB.AddTable(dt, String.Format("SELECT Valid_Value as {0}, Description FROM Validation WHERE Field_Name='{0}' ORDER BY {0}", FieldList))
            Else
                Project.DB.AddTable(dt, String.Format("SELECT DISTINCT {0} FROM {1} ORDER BY {0}", FieldList, TableName))
            End If

        End If

        'apply filter
        Dim Filter As String = txtFilterString.Text
        Dim filterExpression As String = ""
        If Not String.IsNullOrEmpty(Filter.Trim) Then
            For Each s As String In FilterFields.Split(",")
                If filterExpression = "" Then
                Else
                    filterExpression &= " OR "
                End If
                filterExpression &= String.Format("{0} LIKE '%{1}%'", s, Filter.ToUpper)
            Next
        End If

        dt.CaseSensitive = False

        'add table to page

        'but first remove all controls then all rows on old table
        For Each r As TableRow In tblTable.Rows
            For Each c As TableCell In r.Cells
                c.Controls.Clear()
            Next
        Next
        tblTable.Rows.Clear()

        'column headings
        Dim tr As New TableRow
        Dim tc As New TableCell
        tc.Text = "Sel"
        tc.Font.Bold = True
        tc.HorizontalAlign = HorizontalAlign.Center
        tr.Cells.Add(tc)

        For Each dc As DataColumn In dt.Columns
            tc = New TableCell
            tc.Text = GetGlobalResourceObject("WRDB", dc.ColumnName)
            tc.Font.Bold = True
            tc.HorizontalAlign = HorizontalAlign.Center
            tr.Cells.Add(tc)
        Next
        tblTable.Rows.Add(tr)

        'body
        For Each dr As DataRow In dt.[Select](filterExpression)
            tr = New TableRow
            tc = New TableCell
            Dim chk As New CheckBox
            chk.ID = "chk_" & dr.Item(0) 'these must have a unique ID so can restore values on postback
            tc.Controls.Add(chk)
            tc.HorizontalAlign = HorizontalAlign.Center
            tr.Cells.Add(tc)

            For Each item As Object In dr.ItemArray
                tc = New TableCell
                tc.Text = item.ToString
                If TypeOf item Is String Then
                    tc.HorizontalAlign = HorizontalAlign.Left
                ElseIf TypeOf item Is Date Then
                    tc.HorizontalAlign = HorizontalAlign.Center
                Else
                    tc.HorizontalAlign = HorizontalAlign.Right
                End If
                tr.Cells.Add(tc)
            Next
            tblTable.Rows.Add(tr)
        Next
        tblTable.GridLines = GridLines.Both
        tblTable.BorderWidth = New Unit(1, UnitType.Pixel)
        tblTable.Font.Size = New FontUnit(0.85, UnitType.Em)

    End Sub

    Private Sub btnOKSupport_Click(sender As Object, e As EventArgs) Handles btnOKSupport.Click

        Dim TableName As String = HttpContext.Current.Session("TableName")
        Dim txt As TextBox = Nothing
        Select Case TableName
            Case "Stations" : txt = txtStationID
            Case "PCodes" : txt = txtPCode
            Case "CCodes" : txt = txtCCode
            Case "RCodes" : txt = txtRCode
            Case "QCodes" : txt = txtQCode
            Case "LCodes" : txt = txtLCode
        End Select
        If txt Is Nothing Then Exit Sub

        Dim lstSta As New List(Of String)
        For r As Integer = 1 To tblTable.Rows.Count - 1
            If CType(tblTable.Rows(r).Cells(0).Controls(0), CheckBox).Checked Then
                If rblDisplay.SelectedIndex = 1 Then 'groups
                    lstSta.AddRange(tblTable.Rows(r).Cells(2).Text.Split(","))
                Else
                    lstSta.Add(tblTable.Rows(r).Cells(1).Text)
                End If
            End If
        Next

        Dim s As String = ""
        For Each sta As String In lstSta
            s &= IIf(s = "", "IN ('", ", '") & sta & "'"
        Next
        If s <> "" Then s &= ")"

        txt.Text = s

    End Sub

    ''' <summary>
    ''' This PageMethod allows a resource value to be passed to the page so JavaScript can display message in correct language
    ''' </summary>
    <System.Web.Services.WebMethod()>
    Public Shared Function GetResource(key As String) As String
        Dim res As String = HttpContext.GetGlobalResourceObject("WRDB", key, ProjectSettings.Language)
        If String.IsNullOrEmpty(res) Then Throw New ApplicationException("Resource was not found: " & key) Else Return res
    End Function

#End Region

#Region "Assist buttons (values)"

    Private Sub ShowDialogValues()
        UpdateValues()
        ShowDialog(UpdatePanel1, pnlDialogValues, GetGlobalResourceObject("WRDB", "QueryAssist"), 700)
    End Sub

    Private Sub btnDepRes_Click(sender As Object, e As EventArgs) Handles btnDepth.Click, btnResult.Click
        HttpContext.Current.Session("btnValue") = sender
        ShowDialogValues()
    End Sub

    Private Sub btnOKValues_Click(sender As Object, e As EventArgs) Handles btnOKValues.Click
        Dim btnValue As Button = HttpContext.Current.Session("TableName")
        Dim txt As TextBox = IIf(btnValue Is btnDepth, txtDepth, txtResult)
        Select Case rblValues.SelectedValue
            Case "between"
                If IsNumeric(txtValue1.Text) And IsNumeric(txtValue2.Text) Then
                    txt.Text = String.Format("BETWEEN {0} AND {1}", txtValue1.Text, txtValue2.Text)
                End If
            Case "not between"
                If IsNumeric(txtValue1.Text) And IsNumeric(txtValue2.Text) Then
                    txt.Text = String.Format("NOT BETWEEN {0} AND {1}", txtValue1.Text, txtValue2.Text)
                End If
            Case Else
                If IsNumeric(txtValue1.Text) Then
                    txt.Text = String.Format("{0} {1}", rblValues.SelectedValue, txtValue1.Text)
                End If
        End Select
    End Sub

    Private Sub rblValues_SelectedIndexChanged(sender As Object, e As EventArgs) Handles rblValues.SelectedIndexChanged
        ShowDialogValues()
    End Sub

    Private Sub UpdateValues()
        lblValue.Visible = False
        lblMin.Visible = False
        lblMax.Visible = False
        txtValue2.Visible = False
        Select Case rblValues.SelectedValue
            Case "between", "not between"
                lblMin.Visible = True
                lblMax.Visible = True
                txtValue2.Visible = True
            Case Else
                lblValue.Visible = True
        End Select
    End Sub

#End Region

#Region "Assist buttons (dates)"

    Private Sub ShowDialogDates()
        UpdateDates()
        calDate1.Enabled = False
        calDate2.Enabled = False
        Select Case rblDates.SelectedValue
            Case "date"
                calDate1.Enabled = True
            Case "daterange"
                calDate1.Enabled = True
                calDate2.Enabled = True
                calDate2.PopupPosition = AjaxControlToolkit.CalendarPosition.TopLeft 'this doesn't work! just make dialog bigger
        End Select
        ShowDialog(UpdatePanel1, pnlDialogDates, GetGlobalResourceObject("WRDB", "QueryAssist"), 700)
    End Sub

    Private Sub btnDateTime_Click(sender As Object, e As EventArgs) Handles btnDateTime.Click
        ShowDialogDates()
    End Sub

    Private Sub btnOKDates_Click(sender As Object, e As EventArgs) Handles btnOKDates.Click
        Dim dt1, dt2 As Date
        Select Case rblDates.SelectedValue
            Case "date"
                If Date.TryParse(txtDate1.Text, ProjectSettings.Language, Globalization.DateTimeStyles.AssumeLocal, dt1) Then
                    txtDateTime.Text = String.Format("BETWEEN '{0:MM/dd/yyyy 00:00}' AND '{0:MM/dd/yyyy 23:59}'", dt1)
                End If
            Case "monthyear"
                If Val(txtDate1.Text) >= 1 And Val(txtDate1.Text) <= 12 And Val(txtDate2.Text) >= 1900 And Val(txtDate2.Text) <= 2100 Then
                    txtDateTime.Text = "MONTH(Date_Time)=" & Val(txtDate1.Text) & " AND YEAR(Date_Time)=" & Val(txtDate2.Text)
                End If
            Case "month"
                If Val(txtDate1.Text) >= 1 And Val(txtDate1.Text) <= 12 Then
                    txtDateTime.Text = "MONTH(Date_Time)=" & Val(txtDate1.Text)
                End If
            Case "year"
                If Val(txtDate2.Text) >= 1900 And Val(txtDate2.Text) <= 2100 Then
                    txtDateTime.Text = "YEAR(Date_Time)=" & Val(txtDate2.Text)
                End If
            Case "daterange"
                If Date.TryParse(txtDate1.Text, ProjectSettings.Language, Globalization.DateTimeStyles.AssumeLocal, dt1) And
                   Date.TryParse(txtDate2.Text, ProjectSettings.Language, Globalization.DateTimeStyles.AssumeLocal, dt2) Then
                    txtDateTime.Text = String.Format("BETWEEN '{0:MM/dd/yyyy 00:00}' AND '{1:MM/dd/yyyy 23:59}'", dt1, dt2)
                End If
        End Select
    End Sub

    Private Sub rblDates_SelectedIndexChanged(sender As Object, e As EventArgs) Handles rblDates.SelectedIndexChanged
        txtDate1.Text = ""
        txtDate2.Text = ""
        ShowDialogDates()
    End Sub

    Private Sub UpdateDates()
        lblDate.Visible = False
        lblDateMonth.Visible = False
        lblDate1.Visible = False
        lblDateYear.Visible = False
        lblDate2.Visible = False
        txtDate1.Visible = False
        txtDate2.Visible = False
        Select Case rblDates.SelectedValue
            Case "daterange"
                lblDate1.Visible = True
                lblDate2.Visible = True
                txtDate1.Visible = True
                txtDate2.Visible = True
            Case "date"
                lblDate.Visible = True
                txtDate1.Visible = True
            Case "monthyear"
                lblDateMonth.Visible = True
                lblDateYear.Visible = True
                txtDate1.Visible = True
                txtDate2.Visible = True
            Case "month"
                lblDateMonth.Visible = True
                txtDate1.Visible = True
            Case "year"
                lblDateYear.Visible = True
                txtDate2.Visible = True
        End Select
    End Sub

#End Region

End Class
